﻿Imports System.Math
Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class PISCOFINSData
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim valida As String = Session("logado")

        'lblMsg.Visible = False

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        lblModuloItem.Text = "Cadastro de Tipo de Despesa De-Para"

        lblMsg.Text = ""

        If Not Page.IsPostBack Then

            Try


                If (Session("strOperacao") = "C" Or Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Consultar | Alterar | Excluir
                    LoadData()
                End If

                If (Session("strOperacao") = "C") Then ' Consultar
                    btnGravar.Enabled = False
                End If

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End If

    End Sub



    Private Sub LoadData()


        If (Session("strOperacao").ToString = "C" Or Session("strOperacao").ToString = "E") Then ' Consultar | Excluir

        End If

        'lblCodPISCOFINS.Text = Session("intIdTipoDespesa").ToString



        lblCodPISCOFINS.Text = Session("strCodPISCOFINS")
        txtPerPIS.Text = Session("intPercentualPIS").ToString
        txtPerCOFINS.Text = Session("intPercentualCOFINS").ToString

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Try
        '        Dim theQueryString As String = ""

        '        theQueryString = "SELECT CodFornecedorServico" & _
        '                         " FROM v_TipoDespesaDeParaHeader" & _
        '                         " WHERE IdTipoDespesa = " & Session("intIdTipoDespesa").ToString & ";"

        '        Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

        '        theSqlCommand.CommandText = theQueryString
        '        theSqlConnection.Open()

        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        Do While theSqlDataReader.Read()

        '            ddlFornecedorServico.SelectedValue = theSqlDataReader!CodFornecedorServico

        '        Loop

        '        theSqlDataReader.Close()

        '    Catch ex As Exception
        '        lblMsg.Text = "LoadData: " & ex.Message
        '    End Try

        'End Using

    End Sub

    Private Function DataValid() As Boolean

        DataValid = False


        Try

            If (Trim(txtPerPIS.Text) = "") Then
                lblMsg.Text = "% PIS deve ser preenchido."
                Exit Function
            End If

            If (Trim(txtPerCOFINS.Text) = "") Then
                lblMsg.Text = "% COFINS deve ser preenchido."
                Exit Function
            End If

            DataValid = True

        Catch ex As Exception

            lblMsg.Text = "DataValid: " & ex.ToString

        End Try

    End Function

    'Private Function CodeExist(ByVal strConn As String) As Boolean

    '    Dim strDtVigencia As String = ""

    '    CodeExist = False

    '    Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

    '        Try
    '            Dim theQueryString As String

    '            strDtVigencia = Right(txtDtVigencia.Text, 4) & "-" & _
    '                            Mid(txtDtVigencia.Text, 4, 2) & "-" & _
    '                            Left(txtDtVigencia.Text, 2)

    '            theQueryString = "SELECT CodTipoDespesa" & _
    '                             " FROM TipoDespesaDeParaHeader" & _
    '                             " WHERE CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
    '                             " AND CodTipoOperacao = " & IIf(rbEntrada.Checked = True, "'E'", "'S'") & _
    '                             " AND CodTipoDespesa = " & ddlTipoDespesa.SelectedValue & _
    '                             " AND Vigencia = '" & strDtVigencia & "';"

    '            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
    '            theSqlCommand.CommandText = theQueryString

    '            theSqlConnection.Open()
    '            Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

    '            If (theSqlDataReader.Read()) Then
    '                CodeExist = True
    '                lblMsg.Text = "Tipo de Despesa já existe no Sistema."
    '            End If

    '            theSqlDataReader.Close()
    '        Catch ex As Exception
    '            CodeExist = True
    '            lblMsg.Text = "CodeExist: " & ex.Message
    '        End Try

    '    End Using

    'End Function

    Private Function CreateSequence() As Boolean

        CreateSequence = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                Dim theQueryString As String
                theQueryString = "SELECT MAX(CodPISCOFINS) AS MAX_CodPISCOFINS" & _
                             " FROM piscofins   "

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                theSqlCommand.CommandText = theQueryString

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.HasRows = True) Then

                    Do While theSqlDataReader.Read()
                        If Not (IsDBNull(theSqlDataReader(0)) = True) Then
                            lblCodPISCOFINS.Text = (theSqlDataReader(0) + 1)
                        Else
                            lblCodPISCOFINS.Text = 1
                        End If
                    Loop
                Else
                    lblCodPISCOFINS.Text = 1
                End If

                CreateSequence = True

                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = "CreateSequence: " & ex.Message
            End Try

        End Using

    End Function

    Private Function AddRecord(ByVal strConn As String) As Boolean

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String
        Dim strDtVigencia As String = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        AddRecord = False

        txtPerPIS.Text = Replace(txtPerPIS.Text, ",", ".")
        txtPerCOFINS.Text = Replace(txtPerCOFINS.Text, ",", ".")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                Dim theQueryString As String

 
                theQueryString = "INSERT INTO piscofins" & _
                                 " (" & _
                                 " CodPISCOFINS," & _
                                 " PercentualPIS," & _
                                 " PercentualCOFINS,"

                theQueryString = theQueryString & _
                                 " CreateUser," & _
                                 " Created" & _
                                 " )"

                theQueryString = theQueryString & _
                                 " VALUES " & _
                                 " (" & _
                                 " " & lblCodPISCOFINS.Text & "," & _
                                 " " & txtPerPIS.Text & "," & _
                                 " " & txtPerCOFINS.Text & ","

                theQueryString = theQueryString & _
                                 " '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                                 " " & nLastUpdated & "" & _
                                 " );"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                theSqlDataReader.Close()

                AddRecord = True

            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "AddRecord: " & ex.Message
            End Try

        End Using

    End Function

    'Private Function VerifyData(ByVal strConn As String) As Boolean

    '    VerifyData = False

    '    Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

    '        Try
    '            Dim theQueryString As String

    '            theQueryString = "SELECT IdTipoDespesa" & _
    '                             " FROM v_TipoDespesaDeParaDetail" & _
    '                             " WHERE IdTipoDespesa = " & lblCodPISCOFINS.Text & ";"

    '            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

    '            theSqlCommand.CommandText = theQueryString
    '            theSqlConnection.Open()

    '            Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

    '            If (theSqlDataReader.Read()) Then
    '                VerifyData = True
    '                lblMsg.Text = "Exclusão não permitida, existe relação da Despesa " & ddlTipoDespesa.Text & " com items do Tipo de Despesas De-Para."
    '            End If

    '        Catch ex As Exception
    '            VerifyData = True
    '            'theSqlDataReader.Connection.Close()
    '            lblMsg.Text = "VerifyData: " & ex.Message
    '        Finally
    '            theSqlConnection.Close()
    '        End Try

    '    End Using

    'End Function

    Private Function DeleteRecord(ByVal strConn As String) As Boolean

        DeleteRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                Dim theQueryString As String

                theQueryString = "DELETE FROM piscofins" & _
                                 " WHERE CodPISCOFINS = " & lblCodPISCOFINS.Text & ";"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                DeleteRecord = True

            Catch ex As Exception
                lblMsg.Text = "DeleteRecord: " & ex.Message
            Finally
                theSqlConnection.Close()
            End Try

        End Using

    End Function

    Protected Sub btnGravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGravar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.Text = ""


        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (DataValid() = True) Then
                Exit Sub
            End If

 
            If Not (CreateSequence() = True) Then
                Exit Sub
            End If

            If Not (AddRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (DataValid() = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir

            If Not (DeleteRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        End If

        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnGravar.Enabled = False
        btnVoltar.Text = "OK"

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("PISCOFINS.aspx")

    End Sub

 

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

End Class
